﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HT.Data.Intefaces;
using System.Configuration;
using System.Data;
using HT.Data.Objects;
using System.Data.SqlTypes;
//using System.Web.conf;
namespace HT.Data.BussinessLogic
{

    public class HotelManger
    {
        // IDBManager manager = null;
        private static HotelManger _hotelManager;

        HotelManger()
        {
            //manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            //manager.ConnectionString = ConfigurationManager.ConnectionStrings["Test1ConnectionString"].ConnectionString;
        }
        /// <summary>
        /// Method to return  HotelManger Object
        /// </summary>
        /// <returns>Login Object</returns>
        public static HotelManger GetInstance()
        {

            if (_hotelManager == null)
                return new HotelManger();
            else
                return _hotelManager;
        }


        /// <summary>
        /// Returns Rooms Data.
        /// </summary>
        /// <returns></returns>
        public DataSet GetRoomStatus(string startDate, string endDate, string hotelId, string roomID)
        {

            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(2);
            //manager.AddParameters(0, "@StartDate", startDate);
            //manager.AddParameters(1, "@EndDate", endDate);
            manager.AddParameters(0, "@HotelId", hotelId);
            manager.AddParameters(1, "@RoomTypeId", roomID);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_GetRoomStatus");
            manager.Dispose();
            return dsRooms;

        }

        /// <summary>
        /// Returns Rooms Data.
        /// </summary>
        /// <returns></returns>
        public DataSet GetBookingStatus(string startDate, string endDate, string hotelId, string roomID)
        {

            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(3);
            manager.AddParameters(0, "@StartDate", DateTime.Now.Date);
            manager.AddParameters(1, "@EndDate", endDate);

            manager.AddParameters(2, "@RoomTypeId", roomID);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_GetBookingStatus");
            manager.Dispose();
            return dsRooms;


        }
        /// <summary>
        /// Returns Rooms Data.
        /// </summary>
        /// <returns></returns>
        public DataSet Authenticate(string loginID, string password)
        {
            DataSet dsUser = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(2);
            manager.AddParameters(0, "@loginid", loginID);
            manager.AddParameters(1, "@password", password);
            dsUser = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_ValidateUser");
            manager.Dispose();
            return dsUser;
        }

        #region Service


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string SaveService(string serviceName, string createdBy)
        {

            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(2);
            manager.AddParameters(0, "@ServiceName", serviceName);
            manager.AddParameters(1, "@CreatedBy", 1);
            manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_InsertServiceMaster");
            manager.Dispose();
            return string.Empty;
        }


        public DataSet GetAllRoomServices(int serviceId)
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);
            manager.AddParameters(0, "@ServiceId", (serviceId == 0) ? SqlInt32.Null : serviceId);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SelectServiceMaster");
            manager.Dispose();
            return dsRooms;

        }

        public int DeleteService(int id)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);
            manager.AddParameters(0, "@serviceid", id);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_DeleteService");
            manager.Dispose();
            return ret;


        }
        public int UpdateService(int id, string name, string modifiedBy)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(3);
            manager.AddParameters(0, "@ServiceId", id);
            manager.AddParameters(1, "@ServiceName", name);
            manager.AddParameters(2, "@ModifiedBy", 1);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_UpdateServiceMaster");
            manager.Dispose();
            return ret;


        }
        #endregion

        #region Tax


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string SaveTax(string Name, string percent, string createdby)
        {

            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(3);
            manager.AddParameters(0, "@TaxName", Name);
            manager.AddParameters(1, "@TaxPercent", Convert.ToDecimal(percent));
            manager.AddParameters(2, "@CreatedBy", 1);
            manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_InsertTaxMaster");
            manager.Dispose();
            return string.Empty;
        }


        public DataSet GetAllTax(int taxid)
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);
            manager.AddParameters(0, "@TaxId", (taxid == 0) ? SqlInt32.Null : taxid);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SelectTaxMaster");
            manager.Dispose();
            return dsRooms;

        }

        public int DeleteTax(int id)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);
            manager.AddParameters(0, "@Taxid", id);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_DeleteTax");
            manager.Dispose();
            return ret;


        }
        public int UpdateTax(int id, string name, string percent, string createdby)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["Test1ConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(3);
            manager.AddParameters(0, "@TaxId", id);
            manager.AddParameters(1, "@TaxName", name);
            manager.AddParameters(2, "@Percentage", Convert.ToDecimal(percent));
            manager.AddParameters(2, "@ModifiedBy", 1);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_UpdateTaxMaster");
            manager.Dispose();
            return ret;


        }
        #endregion

        #region Hotel


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string SaveHotel(HotelObject ho)
        {

            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();

            manager.CreateParameters(8);

            manager.AddParameters(0, "@HotelName", ho.Name);
            manager.AddParameters(1, "@Address", ho.Address);
            manager.AddParameters(2, "@StarRating", ho.StarRating);
            manager.AddParameters(3, "@Email", ho.Email);
            manager.AddParameters(4, "@Phone", ho.Phone);
            manager.AddParameters(5, "@Fax", ho.Fax);
            manager.AddParameters(6, "@Website", ho.WebSite);
            manager.AddParameters(7, "@CreatedBy", 1);
            manager.ExecuteNonQuery(CommandType.StoredProcedure, "[usp_InsertHotelMaster]");
            manager.Dispose();
            return string.Empty;
        }


        public DataSet GetAllHotel()
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_Rep_GetHotelList");
            manager.Dispose();
            return dsRooms;

        }
        public DataSet GetAllHotel(int hotelid)
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            //  manager.CreateParameters(1);
            //  manager.AddParameters(0, "@HotelId", (hotelid == 0) ? SqlInt32.Null : hotelid);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SelectHotelMaster");
            manager.Dispose();
            return dsRooms;

        }

        public int DeleteHotel(int id)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["Test1ConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);
            manager.AddParameters(0, "@hotelid", id);
            int ret = manager.ExecuteNonQuery(CommandType.Text, "delete from hotel where hotelid=" + id);
            manager.Dispose();
            return ret;


        }
        public int UpdateHotel(HotelObject ho)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(8);
            //manager.AddParameters(0, "@HotelId", ho.Id);
            manager.AddParameters(0, "@HotelName", ho.Name);
            manager.AddParameters(1, "@Address", ho.Address);
            manager.AddParameters(2, "@StarRating", ho.StarRating);
            manager.AddParameters(3, "@Email", ho.Email);
            manager.AddParameters(4, "@Phone", ho.Phone);
            manager.AddParameters(5, "@Fax", ho.Fax);
            manager.AddParameters(6, "@Website", ho.WebSite);
            manager.AddParameters(7, "@ModifiedBy", Convert.ToInt32(ho.modifiedby));
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_UpdateHotelMaster");
            manager.Dispose();
            return ret;


        }
        #endregion


        #region RoomType
        public DataSet GetAllRoomType(int hotelid, int roomid)
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);

            manager.AddParameters(0, "@RoomTypeId", (roomid == 0) ? SqlInt32.Null : roomid);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SelectRoomTypeMaster");
            manager.Dispose();
            return dsRooms;

        }
        #endregion


        #region booking
        public DataSet GetBookingStatus(int hotelid, int roomid)
        {

            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);

            manager.AddParameters(0, "@RoomTypeId", (roomid == 0) ? SqlInt32.Null : roomid);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_GetRoomStatus");
            manager.Dispose();
            return dsRooms;
        }

        #endregion


        #region Booking Status

        public DataSet GetAllBooking(string startdate, string enddate, int hotelid, int roomid)
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(3);
            manager.AddParameters(0, "@StartDate", Convert.ToDateTime(startdate));
            manager.AddParameters(1, "@EndDate ", (enddate == "") ? SqlDateTime.Null : Convert.ToDateTime(enddate));

            manager.AddParameters(2, "@RoomTypeId", (roomid == 0) ? SqlInt32.Null : roomid);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_GetBookingStatus");
            manager.Dispose();
            return dsRooms;

        }

        #endregion

        #region reports
        public DataSet GetReports()
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_Rep_GetHotelList");
            manager.Dispose();
            return dsRooms;

        }
        #endregion

        #region Guest


        public DataSet GetAllGuestBySearch(string firstName, string lastName, string phone, string email)
        {

            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(4);
            manager.AddParameters(0, "@FirstName", (firstName == "") ? SqlString.Null : firstName);
            manager.AddParameters(1, "@LastName", (lastName == "") ? SqlString.Null : lastName);
            manager.AddParameters(2, "@Phone", (phone == "") ? SqlString.Null : phone);
            manager.AddParameters(3, "@Email", (email == "") ? SqlString.Null : email);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SearchGuestMaster");
            manager.Dispose();
            return dsRooms;
        }

        public DataSet GetAllGuest(int guestId)
        {

            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);
            manager.AddParameters(0, "@GuestId", (guestId == 0) ? SqlInt32.Null : guestId);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SelectGuestMaster");
            manager.Dispose();
            return dsRooms;
        }

        public int SaveGuest(Guest guest)
        {

            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(14);
            manager.AddParameters(0, "@FirstName", guest.FirstName);
            manager.AddParameters(1, "@LastName", guest.LastName);
            manager.AddParameters(2, "@BirthDate", guest.BirthDate);
            manager.AddParameters(3, "@Age", guest.Age);
            manager.AddParameters(4, "@Gender", guest.Gender);
            manager.AddParameters(5, "@Nationality", guest.Nationality);
            manager.AddParameters(6, "@Email", guest.Email);
            manager.AddParameters(7, "@CountryCode", guest.CountryCode);
            manager.AddParameters(8, "@Phone", guest.Phone);
            manager.AddParameters(9, "@Occupation", guest.Occupation);
            manager.AddParameters(10, "@Address", guest.Address);


            manager.AddParameters(11, "@HasProof", guest.HasProof);
            manager.AddParameters(12, "@PassportNumber", guest.PassportNumber);

            manager.AddParameters(13, "@CreatedBy", guest.CreatedBy);
            //  manager.AddParameters(0, "@GuestId", (guestId == 0) ? SqlInt32.Null : guestId);

            manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_InsertGuestMaster");
            manager.Dispose();

            return 0;

        }
        public int UpdateGuest(Guest guest)
        {

            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(19);

            manager.AddParameters(0, "@FirstName", guest.FirstName);
            manager.AddParameters(1, "@LastName", guest.LastName);
            manager.AddParameters(2, "@BirthDate", guest.BirthDate);
            manager.AddParameters(3, "@Age", guest.Age);
            manager.AddParameters(4, "@Gender", guest.Gender);
            manager.AddParameters(5, "@Nationality", guest.Nationality);
            manager.AddParameters(6, "@Email", guest.Email);
            manager.AddParameters(7, "@CountryCode", guest.CountryCode);
            manager.AddParameters(8, "@Phone", guest.Phone);
            manager.AddParameters(9, "@Occupation", guest.Occupation);
            manager.AddParameters(10, "@Address", guest.Address);
            manager.AddParameters(11, "@ModeOfTravel", guest.ModeOfTravel);
            manager.AddParameters(12, "@PurposeOfVisit", guest.PurposeOfVisit);
            manager.AddParameters(13, "@HasProof", guest.HasProof);
            manager.AddParameters(14, "@PassportNumber", guest.PassportNumber);
            manager.AddParameters(15, "@ComingFrom", guest.ComingFrom);
            manager.AddParameters(16, "@ProceedingTo", guest.ProceedingTo);
            manager.AddParameters(17, "@ModifiedBy", guest.CreatedBy);
            manager.AddParameters(18, "@GuestId", guest.GuestID);
            manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_UpdateGuestMaster");
            manager.Dispose();

            return 0;

        }

        #endregion

        #region payment mode

        public DataSet GetAllPaymentMode(int paymentmodeid)
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);
            manager.AddParameters(0, "@PaymentModeId", (paymentmodeid == 0) ? SqlInt32.Null : paymentmodeid);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SelectPaymentModeMaster");
            manager.Dispose();
            return dsRooms;

        }

        public int UpdatePaymentMode(int id, string mode, string surcharge, int modifiedBy)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(4);
            manager.AddParameters(0, "@PaymentModeId", id);
            manager.AddParameters(1, "@PaymentModeName", mode);
            manager.AddParameters(2, "@SurchargePercent", (surcharge == "") ? 0 : Convert.ToDecimal(surcharge));
            manager.AddParameters(3, "@ModifiedBy", 1);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_UpdatePaymentModeMaster");
            manager.Dispose();
            return ret;


        }
        public int InsertePaymentMode(string mode, string surcharge, int modifiedBy)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(3);

            manager.AddParameters(0, "@PaymentModeName", mode);
            manager.AddParameters(1, "@SurchargePercent", (surcharge == "") ? 0 : Convert.ToDecimal(surcharge));
            manager.AddParameters(2, "@CreatedBy", 1);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_InsertPaymentModeMaster");
            manager.Dispose();
            return ret;


        }

        #endregion

        #region Room

        public DataSet GetAllRoom(int HotelId, int RoomId)
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);

            manager.AddParameters(0, "@RoomId", (RoomId == 0) ? SqlInt32.Null : RoomId);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SelectRoomMaster");
            manager.Dispose();
            return dsRooms;

        }

        public int UpdateRoom(int id, string number, int roomtypeid, bool IsUnderRoomService, int modifiedBy)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(5);
            manager.AddParameters(0, "@RoomId", id);
            manager.AddParameters(1, "@RoomNumber", number);
            manager.AddParameters(2, "@RoomTypeId", (roomtypeid == 0) ? SqlInt32.Null : roomtypeid);
            manager.AddParameters(3, "@IsUnderRoomService", IsUnderRoomService);
            manager.AddParameters(4, "@ModifiedBy", 1);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_UpdateRoomMaster");
            manager.Dispose();
            return ret;


        }
        public int InserteRoom(string number, int roomtypeid, bool IsUnderRoomService, int modifiedByy)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(4);
            manager.AddParameters(0, "@RoomNumber", number);
            manager.AddParameters(1, "@RoomTypeId", (roomtypeid == 0) ? SqlDecimal.Null : roomtypeid);
            manager.AddParameters(2, "@IsUnderRoomService", IsUnderRoomService);
            manager.AddParameters(3, "@CreatedBy", 1);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_InsertRoomMaster");
            manager.Dispose();
            return ret;


        }

        #endregion


        #region RoomType
        public DataSet GetRoomType(int HotelId, int RoomId)
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);

            manager.AddParameters(0, "@RoomTypeId", (RoomId == 0) ? SqlInt32.Null : RoomId);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SelectRoomTypeMaster");
            manager.Dispose();
            return dsRooms;

        }

        public int UpdateRoomType(RoomType rtype)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(10);

            manager.AddParameters(0, "@RoomTypeId", rtype.RoomTypeId);
            // manager.AddParameters(1, "@HotelId", rtype.HotelId);
            manager.AddParameters(1, "@RoomTypeName", rtype.RoomTypeName);
            manager.AddParameters(2, "@Occupancy", rtype.Occupancy);
            manager.AddParameters(3, "@NonACRoomCharge", rtype.ChargeNonAC);
            manager.AddParameters(4, "@ACRoomCharge", rtype.ChargeAC);
            manager.AddParameters(5, "@IsExtraBedAllowed", rtype.IsExtraBedAllowed);
            manager.AddParameters(6, "@MaxExtraBedsAllowed", rtype.MaxExtraBedsAllowed);
            manager.AddParameters(7, "@NonACExtraBedCharge", rtype.ExtraBedChargeNonAC);
            manager.AddParameters(8, "@ACExtraBedCharge", rtype.ExtraBedChargeAC);
            manager.AddParameters(9, "@ModifiedBy", rtype.ModifiedBy);

            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_UpdateRoomTypeMaster");
            manager.Dispose();
            return ret;


        }
        public int InserteRoomType(RoomType rtype)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(9);
            // manager.AddParameters(0, "@RoomTypeId", rtype.RoomTypeId);
            // manager.AddParameters(0, "@HotelId", rtype.HotelId);
            manager.AddParameters(0, "@RoomTypeName", rtype.RoomTypeName);
            manager.AddParameters(1, "@Occupancy", rtype.Occupancy);
            manager.AddParameters(2, "@NonACRoomCharge", rtype.ChargeNonAC);
            manager.AddParameters(3, "@ACRoomCharge", rtype.ChargeAC);
            manager.AddParameters(4, "@IsExtraBedAllowed", rtype.IsExtraBedAllowed);
            manager.AddParameters(5, "@MaxExtraBedsAllowed", rtype.MaxExtraBedsAllowed);
            manager.AddParameters(6, "@NonACExtraBedCharge", rtype.ExtraBedChargeNonAC);
            manager.AddParameters(7, "@ACExtraBedCharge", rtype.ExtraBedChargeAC);
            manager.AddParameters(8, "@CreatedBy", 1);
            object ret = manager.ExecuteScalar(CommandType.StoredProcedure, "usp_InsertRoomTypeMaster");

            manager.Dispose();
            return int.Parse(ret.ToString());


        }
        #endregion

        #region RoomTypeTax
        public int SaveRoomTypeTax(int roomtypeid, int taxid)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(3);
            manager.AddParameters(0, "@RoomTypeId", roomtypeid);
            manager.AddParameters(1, "@taxId", taxid);
            manager.AddParameters(2, "@CreatedBy", 1);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_InsertRoomTypeTax");
            manager.Dispose();
            return ret;


        }

        public int SaveRoomTypeService(int roomtypeid, int serviceid)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(3);
            manager.AddParameters(0, "@RoomTypeId", roomtypeid);
            manager.AddParameters(1, "@ServiceId", serviceid);
            manager.AddParameters(2, "@CreatedBy", 1);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_InsertRoomTypeService");
            manager.Dispose();
            return ret;


        }

        public int RemoveRoomTypeTax(int roomtypeid)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);
            manager.AddParameters(0, "@RoomTypeId", roomtypeid);

            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_RemoveRoomTypeTax");
            manager.Dispose();
            return ret;


        }

        public int RemoveRoomTypeService(int roomtypeid)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);
            manager.AddParameters(0, "@RoomTypeId", roomtypeid);

            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_RemoveRoomTypeService");
            manager.Dispose();
            return ret;


        }
        #endregion

        #region Clear satus
        public int ClearRoomStatus(int roomeid, int modifiedBy)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(2);
            manager.AddParameters(0, "@RoomId", roomeid);
            manager.AddParameters(1, "@ModifiedBy", 1);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_ClearRoomStatus");
            manager.Dispose();
            return ret;


        }

        #endregion

        #region Payments
        public DataSet GetPayments(int PaymentModeId, string clearFlag, string startdate, string enddate)
        {
            DataSet ds = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(4);
            manager.AddParameters(0, "@PaymentModeId", (PaymentModeId == 0) ? SqlInt32.Null : PaymentModeId);
            manager.AddParameters(1, "@ClearanceFlag", (clearFlag == "") ? SqlBoolean.Null : Convert.ToBoolean(Convert.ToInt32(clearFlag)));
            DateTime dt = Convert.ToDateTime(startdate);
            manager.AddParameters(2, "@StartDate", dt.Month + "/" + dt.Day + "/" + dt.Year);
            DateTime dt1 = Convert.ToDateTime(enddate);
            manager.AddParameters(3, "@EndDate", dt1.Month + "/" + dt1.Day + "/" + dt1.Year);
            ds = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SearchPayments");
            manager.Dispose();
            return ds;

        }

        public int UpdatePayments(Payments rtype)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(9);
            // manager.AddParameters(0, "@RoomTypeId", rtype.RoomPTypeId);
            // manager.AddParameters(0, "@HotelId", rtype.HotelId);
            manager.AddParameters(0, "@PaymentId", rtype.PaymentId);
            manager.AddParameters(1, "@PaymentModeId", rtype.PaymentModeId);
            manager.AddParameters(2, "@PaymentDate", rtype.PaymentDate);
            manager.AddParameters(3, "@PaymentDescription", rtype.PaymentDescription);
            manager.AddParameters(4, "@Amount", rtype.Amount);
            manager.AddParameters(5, "@Surcharge", rtype.Surcharge);
            manager.AddParameters(6, "@ClearanceFlag", rtype.ClearanceFlag);
            manager.AddParameters(7, "@Remarks", rtype.Remarks);
            manager.AddParameters(8, "@ModifiedBy", rtype.ModifiedBy);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_UpdatePayment");
            manager.Dispose();
            return ret;


        }
        #endregion
        #region Expense
        public DataSet GetExpense(string startdate, string enddate)
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(2);
            manager.AddParameters(0, "@StartDate", startdate);
            manager.AddParameters(1, "@EndDate", enddate);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SearchExpenses");
            manager.Dispose();
            return dsRooms;

        }

        public int UpdateExpense(Expense expense)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(6);
            manager.AddParameters(0, "@ExpenseId", expense.ExpenseId);
            manager.AddParameters(1, "@ExpenseDate", expense.ExpenseDate);
            manager.AddParameters(2, "@ExpenseDescription", expense.ExpenseDescription);
            manager.AddParameters(3, "@Amount", expense.Amount);
            manager.AddParameters(4, "@Remarks", expense.Remarks);
            manager.AddParameters(5, "@ModifiedBy", expense.ModifiedBy);


            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_UpdateExpense");
            manager.Dispose();
            return ret;


        }

        public int SaveExpense(Expense expense)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(5);
            manager.AddParameters(0, "@ExpenseDate", expense.ExpenseDate);
            manager.AddParameters(1, "@ExpenseDescription", expense.ExpenseDescription);
            manager.AddParameters(2, "@Amount", expense.Amount);
            manager.AddParameters(3, "@Remarks", expense.Remarks);
            manager.AddParameters(4, "@CreatedBy", expense.ModifiedBy);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_InsertExpense");
            manager.Dispose();
            return ret;


        }
        #endregion
        #region booking


        public DataSet GetAllBooking(string bookingNo, int roomtypeid, string startdate, string enddate)
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(4);
            manager.AddParameters(0, "@BookingNumber", bookingNo);
            manager.AddParameters(1, "@RoomTypeId", (roomtypeid == 0) ? SqlInt32.Null : roomtypeid);
            manager.AddParameters(2, "@StartDate", startdate);
            manager.AddParameters(3, "@EndDate", enddate);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SearchBookings");
            manager.Dispose();
            return dsRooms;

        }

        public DataSet GetAllInfo(int bookingId)
        {
            DataSet dsRooms = null;
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(1);
            manager.AddParameters(0, "@BookingId", (bookingId == 0) ? SqlInt32.Null : bookingId);
            dsRooms = manager.ExecuteDataSet(CommandType.StoredProcedure, "usp_SelectBooking");
            manager.Dispose();
            return dsRooms;
        }

        public int UpdateBooking(HT.Data.Objects.Booking booking)
        {
            IDBManager manager = new DB.DBManager(HT.Data.DB.Util.DataProvider.SqlServer);
            manager.ConnectionString = ConfigurationManager.ConnectionStrings["hoteldbConnectionString"].ConnectionString;
            manager.Open();
            manager.CreateParameters(23);
            manager.AddParameters(0, "@BookingId", booking.BookingId);
            manager.AddParameters(1, "@BookingNumber", booking.BookingNumber);
            manager.AddParameters(2, "@RoomTypeId", booking.RoomTypeId);
            manager.AddParameters(3, "@GuestId", booking.GuestId);
            manager.AddParameters(4, "@ExpectedCheckInDate", booking.ExpectedCheckInDate);
            manager.AddParameters(5, "@LengthOfStay", booking.LengthOfStay);
            manager.AddParameters(6, "@PickupRequired", booking.PickupRequired);
            manager.AddParameters(7, "@AdvancePaymentId", booking.AdvancePaymentId);
            manager.AddParameters(8, "@AdvanceAmount", booking.AdvanceAmount);
            manager.AddParameters(9, "@AdvancePaymetnDate", booking.AdvancePaymentDate);
            manager.AddParameters(10, "@AdvanceDescription", booking.AdvancePaymentDescription);
            manager.AddParameters(11, "@AdvancePaymentModeId", booking.AdvancePaymentModeId);
            manager.AddParameters(12, "@AdvanceSurchage", booking.AdvancePaymentSurcharge);
            manager.AddParameters(13, "@AdvanceClearanceFlag", booking.AdvanceClearanceFlag);
            manager.AddParameters(14, "@AdvanceRemarks", booking.AdvancePaymentDescription);//
            manager.AddParameters(15, "@CancelPaymentId", booking.CancelPaymentId);
            manager.AddParameters(16, "@CancelReason", booking.CancelReason);
            manager.AddParameters(17, "@CancellationFee", booking.CancelFeePaymentModeId);
            manager.AddParameters(18, "@CancelDate", booking.CancelledDate);
            manager.AddParameters(19, "@CancellationPaymentModeId", booking.CancelFeePaymentModeId);
            manager.AddParameters(20, "@CancellationSurcharge", booking.CalcelFeeSurchage);
            manager.AddParameters(21, "@CancellationFeeClearanceFlag", booking.CancellationClearanceflag);
            manager.AddParameters(22, "@ModifiedBy", 1);
            int ret = manager.ExecuteNonQuery(CommandType.StoredProcedure, "usp_UpdateBooking");
            manager.Dispose();
            return ret;

        }
        #endregion
    }
}
